

**************************************************************************************************
* validate builder distress: correlate builder distress with TOTAL builder shock across counties 
**************************************************************************************************
 
set more off

local file_path_in "/tochange/"

use "/`file_path_in'//HOUSEBUILDERshockHHI.dta", clear
	keep if error==0
	keep if small==0
	keep if year==2009  

	* calc sales per builder in all of 2009
	g one = 1
	bysort seller: g nsales = sum(one)
	bysort seller: replace nsales = nsales[_N]
		
** pull in finances 	
	merge m:1 seller year quarter using  "/`file_path_in'//proj/eod322/Compustat/builderfinq.dta",  keep(master match)
	drop _merge
	gen lnassets_a=log(assets_a)
	gen lnassets_q=log(assets_q)
	
** define financial ratios
g profit_margin_a = net_income_a / revt_a
g asset_turnover = revt_a / assets_a

** correlations of builder profits with TOTAL shock at builder level
	keep if coverage_q06_mean != .
	collapse net_income profitability_a ebit_a  profit_margin asset_turnover revt_a dep4_0609_fips lnassets_a  nsales , by(seller)
		
		label var net_income "Net Income, 09"
		label var profitability_a "Profitability, 09"
		label var ebit_a "EBIT, 09"
		label var profit_margin "Profit Margin, 09"
		label var asset_turnover "Asset Turnover, 09"
		label var dep4_0609_fips "Total Builder Shock"
		
		** reg table of correlations
		reg net_income dep4_0609_fips   [aw=nsales]
		est sto c1
		reg profitability_a dep4_0609_fips   [aw=nsales]
		est sto c2
		reg ebit_a dep4_0609_fips   [aw=nsales]
		est sto c3
		reg profit_margin_a dep4_0609_fips   [aw=nsales]
		est sto c4
		reg asset_turnover dep4_0609_fips   [aw=nsales]
		est sto c5
		
		esttab  c2 c3 c4 c5 using "/`file_path_in'//reg output/builder_distress_validation.tex",   replace se label s(r2 N , label("\$ R^2 \$")) substitute(\_ _) mtitles("Profitability" "EBIT" "Profit Margin" "Asset Turnover") varwidth(1) nonotes   starlevels(* 0.10 ** 0.05 *** 0.01)
		esttab  c2 c3 c4 c5 using "/`file_path_in'//reg output/builder_distress_validation.csv",   replace se label s(r2 N , label("\$ R^2 \$")) substitute(\_ _) mtitles("Profitability" "EBIT" "Profit Margin" "Asset Turnover") varwidth(1) nonotes   starlevels(* 0.10 ** 0.05 *** 0.01)				
				
		** pic of correlations
		binscatter net_income dep4_0609_fips   [aw=nsales], saving("/`file_path_in'/reg output/net_income_shock.gph", replace) ytitle("Net Income, 09") xtitle("Total Builder Shock, 09")
		graph export "/`file_path_in'/reg output//net_income_shock.png", replace
		binscatter profitability_a dep4_0609_fips   [aw=nsales] , saving("/`file_path_in'//reg output/profitability_shock.gph", replace) ytitle("Profitability, 09") xtitle("Total Builder Shock, 09")
		graph export "/`file_path_in'/reg output//profitability_shock.png", replace
		binscatter ebit_a dep4_0609_fips   [aw=nsales], saving("/`file_path_in'//reg output/ebit_shock.gph", replace) ytitle("EBIT, 09") xtitle("Total Builder Shock, 09")
		graph export "/`file_path_in'/reg output//ebit_shock.png", replace
		binscatter profit_margin dep4_0609_fips   [aw=nsales], saving("/`file_path_in'//reg output/profit_margin_shock.gph", replace) ytitle("Profit Margin, 09") xtitle("Total Builder Shock, 09")
		graph export "/`file_path_in'/reg output//profit_margin_shock.png", replace
		binscatter asset_turnover dep4_0609_fips   [aw=nsales], saving("/`file_path_in'//reg output/asset_turnover_shock.gph", replace) ytitle("Asset Turnover, 09") xtitle("Total Builder Shock, 09")
		graph export "/`file_path_in'/reg output//asset_turnover_shock.png", replace
			
		graph combine  "/`file_path_in'/reg output//profitability_shock.gph" "/`file_path_in'/reg output//ebit_shock.gph" "/`file_path_in'/reg output//profit_margin_shock.gph"	"/`file_path_in'/reg output//asset_turnover_shock.gph",  graphregion(color(white)) 
		graph export  "/`file_path_in'/reg output//builder_distress_validation.png", replace
		






